﻿DROP SCHEMA public;

CREATE SCHEMA public;

------------------------------------------------------------------------------------
-- TABLES
------------------------------------------------------------------------------------

--
-- ARTICLES
--
CREATE SEQUENCE article_id_seq;
CREATE TABLE article (
    article_id integer DEFAULT nextval('article_id_seq') NOT NULL,
    title character varying(255),
    pub_year character(4),
    citation character varying(255),
    article_type_id integer,
    url character varying(255),
    pm_id character varying(50),
    article_import_ref integer,
CONSTRAINT pk_article PRIMARY KEY (article_id),
CONSTRAINT un_article UNIQUE(title, pub_year)  
);

CREATE SEQUENCE article_type_id_seq;
CREATE TABLE article_type (
    article_type_id integer DEFAULT nextval('article_type_id_seq') NOT NULL,
    article_type_name character varying(255) NOT NULL,
CONSTRAINT pk_article_type PRIMARY KEY (article_type_id),
CONSTRAINT un_article_type UNIQUE(article_type_name) 
);

CREATE SEQUENCE author_id_seq;
CREATE TABLE author (
    author_id integer DEFAULT nextval('author_id_seq') NOT NULL,
    title character varying(50),
    last_name character varying(50),
    first_name character varying(50),
    mid_name character(5),
    suffix character varying(15),
    url character varying(255),
CONSTRAINT pk_author PRIMARY KEY (author_id),
CONSTRAINT un_author UNIQUE(last_name, first_name, mid_name) 
);

--
-- DATABASE
--
CREATE SEQUENCE database_id_seq;
CREATE TABLE database (
    database_id integer DEFAULT nextval('database_id_seq') NOT NULL,
    database_name character varying(20),
    database_note character varying(1000),
CONSTRAINT pk_database PRIMARY KEY (database_id),
CONSTRAINT un_database UNIQUE(database_name) 
);

--
-- BINDING
--
CREATE SEQUENCE binding_id_seq;
CREATE TABLE binding (
    binding_id integer DEFAULT nextval('binding_id_seq') NOT NULL,
    ligand_id integer NOT NULL,
    lectin_id integer,
    fimbria_id integer,
    --strain_id integer NOT NULL,
    binding_notes character varying(255),
    binding_strength_id integer,
    --method_id integer,
    database_id integer NOT NULL,
CONSTRAINT pk_binding PRIMARY KEY (binding_id),
CONSTRAINT un_binding UNIQUE(ligand_id, lectin_id, fimbria_id, binding_notes, database_id)
);
-- partial index to avoid duplication due to NULL value
-- CREATE UNIQUE INDEX un_idx_binding ON binding (ligand_id, COALESCE(lectin_id, -1), COALESCE(fimbria_id, -1), COALESCE(database_id, -1), COALESCE(binding_notes, '-1')); 



CREATE SEQUENCE binding_strength_id_seq;
CREATE TABLE binding_strength (
    binding_strength_id integer DEFAULT nextval('binding_strength_id_seq') NOT NULL,
    binding_strength_name character varying(50),
CONSTRAINT pk_binding_strength PRIMARY KEY (binding_strength_id),
CONSTRAINT un_binding_strength UNIQUE(binding_strength_name) 
);

--
-- FIMBRIA
--
CREATE SEQUENCE fimbria_id_seq;
CREATE TABLE fimbria (
    fimbria_id integer DEFAULT nextval('fimbria_id_seq') NOT NULL,
    fimbria_name character varying(50),
    --gene_name character varying(50),
    --uniprot_id character varying(50),
CONSTRAINT pk_fimbria PRIMARY KEY (fimbria_id),
CONSTRAINT un_fimbria UNIQUE(fimbria_name) 
);


--
-- LECTIN
--
CREATE SEQUENCE lectin_id_seq;
CREATE TABLE lectin (
    lectin_id integer DEFAULT nextval('lectin_id_seq') NOT NULL,
    lectin_name character varying(50),
    gene_name character varying(50),
    cermav_ref character varying(50),
CONSTRAINT pk_lectin PRIMARY KEY (lectin_id),
CONSTRAINT un_lectin UNIQUE(lectin_name) 
);

CREATE SEQUENCE lectin_uniprot_id_seq;
CREATE TABLE lectin_uniprot (
    lectin_uniprot_id integer DEFAULT nextval('lectin_uniprot_id_seq') NOT NULL,
    lectin_id integer NOT NULL,
    uniprot_id character varying(50) NOT NULL,
CONSTRAINT pk_lectin_uniprot PRIMARY KEY (lectin_uniprot_id),
CONSTRAINT un_lectin_uniprot UNIQUE(lectin_id, uniprot_id)  
);

--
-- LIGAND
--
CREATE SEQUENCE ligand_id_seq;
CREATE TABLE ligand (
    ligand_id integer DEFAULT nextval('ligand_id_seq') NOT NULL,
    ligand_type_id integer,
    structure_id integer,
    --author_nomenclature character varying(255),
    full_name character varying(255),
    abbrev_name character varying(50),
    --source_id integer,
    ligand_notes character varying(255),
    ligand_ref character varying(255),
CONSTRAINT pk_ligand PRIMARY KEY (ligand_id),
CONSTRAINT un_ligand UNIQUE(structure_id, full_name) 
);

CREATE SEQUENCE ligand_type_id_seq;
CREATE TABLE ligand_type (
    ligand_type_id integer DEFAULT nextval('ligand_type_id_seq') NOT NULL,
    ligand_type_name character varying(255),    
    ligand_type_parent_id integer,
CONSTRAINT pk_ligand_type PRIMARY KEY (ligand_type_id),
CONSTRAINT un_ligand_type UNIQUE(ligand_type_name) 
);


CREATE SEQUENCE ligand_synonym_id_seq;
CREATE TABLE ligand_synonym (
    ligand_synonym_id integer DEFAULT nextval('ligand_synonym_id_seq') NOT NULL,
    ligand_synonym_name character varying(50),
    ligand_id integer,    
CONSTRAINT pk_ligand_synonym PRIMARY KEY (ligand_synonym_id),
CONSTRAINT un_ligand_synonym UNIQUE(ligand_id, ligand_synonym_name) 
);

CREATE SEQUENCE structure_id_seq;
CREATE TABLE structure ( --nomenclature
    structure_id integer DEFAULT nextval('structure_id_seq') NOT NULL,
    iupac character varying(255),
    glycoct character varying(1000),
    --pic_path character varying(255),
    glycosuite_id integer,
    cfg bytea,
    uoxfcol bytea,
    text bytea,
CONSTRAINT pk_structure PRIMARY KEY (structure_id),
CONSTRAINT un_structure UNIQUE(iupac) 
);

/*
CREATE SEQUENCE source_id_seq;
CREATE TABLE source ( 
    source_id integer DEFAULT nextval('source_id_seq') NOT NULL,
    source_organism_id integer NOT NULL, 
    source_name character varying(255),
    source_parent_id integer,	
CONSTRAINT pk_source PRIMARY KEY (source_id),
CONSTRAINT un_source UNIQUE(source_organism_id, source_name) 
);

CREATE SEQUENCE source_organism_id_seq;
CREATE TABLE source_organism ( 
    source_organism_id integer DEFAULT nextval('source_organism_id_seq') NOT NULL,
    source_organism_name character varying(255),
CONSTRAINT pk_source_organism PRIMARY KEY (source_organism_id),
CONSTRAINT un_source_organism UNIQUE(source_organism_name) 
);
*/

--
-- PATHOGEN
--
CREATE SEQUENCE pathogen_id_seq;
CREATE TABLE pathogen (
    pathogen_id integer DEFAULT nextval('pathogen_id_seq') NOT NULL,
    pathogen_name character varying(255),
    pathogen_type_id integer,
    pathogen_notes character varying(1000),
    taxonomy_id integer,
    pathogen_ref character varying(255),
CONSTRAINT pk_pathogen PRIMARY KEY (pathogen_id),
CONSTRAINT un_pathogen UNIQUE(pathogen_name) 
);

CREATE SEQUENCE pathogen_synonym_id_seq;
CREATE TABLE pathogen_synonym (
    pathogen_synonym_id integer DEFAULT nextval('pathogen_synonym_id_seq') NOT NULL,
    pathogen_synonym_name character varying(50),
    pathogen_id integer,    
CONSTRAINT pk_pathogen_synonym PRIMARY KEY (pathogen_synonym_id),
CONSTRAINT un_pathogen_synonym UNIQUE(pathogen_id, pathogen_synonym_name) 
);

CREATE SEQUENCE pathogen_type_id_seq;
CREATE TABLE pathogen_type (
    pathogen_type_id integer DEFAULT nextval('pathogen_type_id_seq') NOT NULL,
    pathogen_type_name character varying(50),
CONSTRAINT pk_pathogen_type PRIMARY KEY (pathogen_type_id),
CONSTRAINT un_pathogen_type UNIQUE(pathogen_type_name) 
);

CREATE SEQUENCE strain_id_seq;
CREATE TABLE strain (
    strain_id integer DEFAULT nextval('strain_id_seq') NOT NULL,
    pathogen_id integer,
    strain_name character varying(255) DEFAULT 'Unknown', --when importing -> add article_id to strain_name 
    taxonomy_id integer,
    article_import_ref integer,
CONSTRAINT pk_strain PRIMARY KEY (strain_id)
--CONSTRAINT un_strain UNIQUE(pathogen_id, strain_name, article_import_ref) 
);


--
-- ORGANISM (TARGET/SOURCE)
--
CREATE SEQUENCE area_id_seq;
CREATE TABLE area (
    area_id integer DEFAULT nextval('area_id_seq') NOT NULL,
    area_type_id integer NOT NULL,
    organism_id integer,
    area_name character varying(255),
    area_ref character varying(255),
    area_parent_id integer,
CONSTRAINT pk_area PRIMARY KEY (area_id),
CONSTRAINT un_area UNIQUE(area_type_id, area_name,organism_id)
);
--partial index to avoid duplication due to NULL value
CREATE UNIQUE INDEX un_idx_area ON area (area_type_id, area_name,COALESCE(organism_id, -1));  

CREATE SEQUENCE area_type_id_seq;
CREATE TABLE area_type (
    area_type_id integer DEFAULT nextval('area_type_id_seq') NOT NULL,
    area_type_name character varying(255),
CONSTRAINT pk_area_type PRIMARY KEY (area_type_id),
CONSTRAINT un_area_type UNIQUE(area_type_name) 
);

CREATE SEQUENCE organism_id_seq;
CREATE TABLE organism (
    organism_id integer DEFAULT nextval('organism_id_seq') NOT NULL,
    organism_name character varying(255) NOT NULL,
    organism_notes character varying(1000) NULL,
    taxonomy_id integer,
    organism_parent_id integer,
CONSTRAINT pk_organism PRIMARY KEY (organism_id),
CONSTRAINT un_organism UNIQUE(organism_name, organism_notes) 
);

--
-- DISEASE
--
CREATE SEQUENCE disease_id_seq;
CREATE TABLE disease (
    disease_id integer DEFAULT nextval('disease_id_seq') NOT NULL,
    disease_name character varying(150),-- DEFAULT 'Unknown', --when importing -> add article_id to disease_name 
    disease_ref character varying(100),
CONSTRAINT pk_disease PRIMARY KEY (disease_id),
CONSTRAINT un_disease UNIQUE(disease_name)
);

CREATE SEQUENCE disease_synonym_id_seq;
CREATE TABLE disease_synonym (
    disease_synonym_id integer DEFAULT nextval('disease_synonym_id_seq') NOT NULL,
    disease_synonym_name character varying(50),
    disease_id integer,    
CONSTRAINT pk_disease_synonym PRIMARY KEY (disease_synonym_id),
CONSTRAINT un_disease_synonym UNIQUE(disease_id, disease_synonym_name)
);

--
-- SYMPTOM
--

CREATE SEQUENCE disease_symptom_studied_id_seq;
CREATE TABLE disease_symptom_studied (
    disease_symptom_studied_id integer DEFAULT nextval('disease_symptom_studied_id_seq') NOT NULL,
    disease_symptom_studied_name character varying(50),
    disease_symptom_studied_notes character varying(255),
    disease_id integer NOT NULL,	
CONSTRAINT pk_disease_symptom_studied PRIMARY KEY (disease_symptom_studied_id),
CONSTRAINT un_disease_symptom_studied UNIQUE(disease_symptom_studied_name)
);




--
-- METHOD (cf glycodb.METHOD)
--
CREATE SEQUENCE method_id_seq;
CREATE TABLE method (
    method_id integer DEFAULT nextval('method_id_seq') NOT NULL,
    method_name character varying(150),
CONSTRAINT pk_method PRIMARY KEY (method_id),
CONSTRAINT un_method UNIQUE(method_name)
);

--
-- SYSTEM
--

CREATE TABLE system (
    dateupdated timestamp with time zone,
    updatedby character varying(50)
);

------------------------------------------------------------------------------------
-- ASSOCIATIVE TABLES
------------------------------------------------------------------------------------
CREATE SEQUENCE disease_area_strain_id_seq;
CREATE TABLE disease_area_strain (
    disease_area_strain_id integer DEFAULT nextval('disease_area_strain_id_seq') NOT NULL,
    disease_id integer NOT NULL,
    area_id integer NOT NULL,
    strain_id integer NOT NULL,
CONSTRAINT pk_disease_area_strain PRIMARY KEY (disease_area_strain_id),
CONSTRAINT un_disease_area_strain UNIQUE (disease_id, area_id, strain_id)
);


CREATE SEQUENCE author_article_id_seq;
CREATE TABLE author_article (
    author_article_id integer DEFAULT nextval('author_article_id_seq') NOT NULL,
    article_id integer NOT NULL,
    author_id integer NOT NULL,
    organization character varying(255),
    is_primary boolean DEFAULT FALSE NOT NULL,
    is_last boolean DEFAULT FALSE NOT NULL,
CONSTRAINT pk_author_article PRIMARY KEY (author_article_id),
CONSTRAINT un_author_article UNIQUE (article_id, author_id)
);

--CREATE SEQUENCE binding_method_id_seq;
CREATE TABLE binding_method (
    --binding_method_id integer DEFAULT nextval('binding_method_id_seq') NOT NULL,
    binding_id integer,
    method_id integer,
CONSTRAINT pk_binding_method PRIMARY KEY (binding_id, method_id),
CONSTRAINT un_binding_method UNIQUE(binding_id, method_id)
);

CREATE TABLE ligand_area (
    --binding_method_id integer DEFAULT nextval('binding_method_id_seq') NOT NULL,
    ligand_id integer,
    area_id integer,
CONSTRAINT pk_ligand_area PRIMARY KEY (ligand_id, area_id),
CONSTRAINT un_ligand_area UNIQUE(ligand_id, area_id)
);


CREATE SEQUENCE binding_strain_article_id_seq;
CREATE TABLE binding_strain_article (
    binding_strain_article_id integer DEFAULT nextval('binding_strain_article_id_seq') NOT NULL,    
    binding_id integer NOT NULL,
    strain_id integer NOT NULL,
    article_id integer NOT NULL,
CONSTRAINT pk_binding_strain_article PRIMARY KEY (binding_strain_article_id),
CONSTRAINT un_binding_strain_article UNIQUE (binding_id, strain_id, article_id)
--CONSTRAINT un_binding_strain_article UNIQUE (binding_id, article_id)
--CONSTRAINT un_binding_strain_article_binding UNIQUE (binding_id)
);


------------------------------------------------------------------------------------
-- PK CONSTRAINTS
------------------------------------------------------------------------------------

-- encoded in table creation 

------------------------------------------------------------------------------------
-- INDEX
------------------------------------------------------------------------------------

--CREATE UNIQUE INDEX idx_bind_strain ON bind_strain USING btree (binding_id, strain_id);


------------------------------------------------------------------------------------
-- FK CONSTRAINTS
------------------------------------------------------------------------------------


---------------ON UPDATE CASCADE ON DELETE SET NULL
--
-- ARTICLE
--
ALTER TABLE ONLY article
    ADD CONSTRAINT fk_article_article_type FOREIGN KEY (article_type_id) REFERENCES article_type(article_type_id);

ALTER TABLE ONLY author_article
    ADD CONSTRAINT fk_author_article_article FOREIGN KEY (article_id) REFERENCES article(article_id);

ALTER TABLE ONLY author_article
    ADD CONSTRAINT fk_author_article_author FOREIGN KEY (author_id) REFERENCES author(author_id);

--
-- BINDING
--
ALTER TABLE ONLY binding
    ADD CONSTRAINT fk_binding_database FOREIGN KEY (database_id) REFERENCES database(database_id);

ALTER TABLE ONLY binding
    ADD CONSTRAINT fk_binding_ligand FOREIGN KEY (ligand_id) REFERENCES ligand(ligand_id);

ALTER TABLE ONLY binding
    ADD CONSTRAINT fk_binding_lectin FOREIGN KEY (lectin_id) REFERENCES lectin(lectin_id);

ALTER TABLE ONLY binding
    ADD CONSTRAINT fk_binding_fimbria FOREIGN KEY (fimbria_id) REFERENCES fimbria(fimbria_id);

ALTER TABLE ONLY binding
    ADD CONSTRAINT fk_binding_binding_strength FOREIGN KEY (binding_strength_id) REFERENCES binding_strength(binding_strength_id);



ALTER TABLE ONLY binding_method
    ADD CONSTRAINT fk_binding_method_method FOREIGN KEY (method_id) REFERENCES method (method_id);

ALTER TABLE ONLY binding_method
    ADD CONSTRAINT fk_binding_method_binding FOREIGN KEY (binding_id) REFERENCES binding (binding_id);


--
-- LIGAND
--
ALTER TABLE ONLY ligand
    ADD CONSTRAINT fk_ligand_structure FOREIGN KEY (structure_id) REFERENCES structure(structure_id);
/*
ALTER TABLE ONLY ligand
    ADD CONSTRAINT fk_ligand_source FOREIGN KEY (source_id) REFERENCES source(source_id);
*/

ALTER TABLE ONLY ligand
    ADD CONSTRAINT fk_ligand_ligand_type FOREIGN KEY (ligand_type_id) REFERENCES ligand_type(ligand_type_id);


ALTER TABLE ONLY ligand_synonym
    ADD CONSTRAINT fk_ligand_synonym_ligand FOREIGN KEY (ligand_id) REFERENCES ligand(ligand_id);


ALTER TABLE ONLY ligand_type
    ADD CONSTRAINT fk_ligand_type_ligand_type FOREIGN KEY (ligand_type_parent_id) REFERENCES ligand_type(ligand_type_id);

--
-- SOURCE
--

/*
ALTER TABLE ONLY source
    ADD CONSTRAINT fk_source_source_organism FOREIGN KEY (source_organism_id) REFERENCES source_organism(source_organism_id);
*/

--ALTER TABLE ONLY source
--    ADD CONSTRAINT fk_area_area FOREIGN KEY (area_parent_id) REFERENCES area(area_id);

--
-- PATHOGEN
--
ALTER TABLE ONLY pathogen
    ADD CONSTRAINT fk_pathogen_pathogen_type FOREIGN KEY (pathogen_type_id) REFERENCES pathogen_type(pathogen_type_id);

ALTER TABLE ONLY pathogen_synonym
    ADD CONSTRAINT fk_pathogen_synonym_pathogen FOREIGN KEY (pathogen_id) REFERENCES pathogen(pathogen_id);

--
-- LECTIN UNIPROT
--

ALTER TABLE ONLY lectin_uniprot
    ADD CONSTRAINT fk_lectin_uniprot_lectin FOREIGN KEY (lectin_id) REFERENCES lectin(lectin_id);

--
-- FIMBRIA UNIPROT
--
/*
ALTER TABLE ONLY fimbria_uniprot
    ADD CONSTRAINT fk_fimbria_uniprot_fimbria FOREIGN KEY (fimbria_id) REFERENCES fimbria(fimbria_id);
*/

--
-- STRAIN
--
ALTER TABLE ONLY strain
    ADD CONSTRAINT fk_strain_pathogen FOREIGN KEY (pathogen_id) REFERENCES pathogen(pathogen_id);


--
-- AREA
--
ALTER TABLE ONLY area
    ADD CONSTRAINT fk_area_organism FOREIGN KEY (organism_id) REFERENCES organism(organism_id);

ALTER TABLE ONLY area
    ADD CONSTRAINT fk_area_area_type FOREIGN KEY (area_type_id) REFERENCES area_type(area_type_id);

ALTER TABLE ONLY area
    ADD CONSTRAINT fk_area_area FOREIGN KEY (area_parent_id) REFERENCES area(area_id);



--
-- DISEASE
--
ALTER TABLE ONLY disease_synonym
    ADD CONSTRAINT fk_disease_synonym_disease FOREIGN KEY (disease_id) REFERENCES disease(disease_id);


ALTER TABLE ONLY disease_symptom_studied
    ADD CONSTRAINT fk_disease_symptom_studied_disease FOREIGN KEY (disease_id) REFERENCES disease(disease_id);

--
-- LIGAND - AREA
--
ALTER TABLE ONLY ligand_area
    ADD CONSTRAINT fk_ligand_area_ligand FOREIGN KEY (ligand_id) REFERENCES ligand(ligand_id);

ALTER TABLE ONLY ligand_area
    ADD CONSTRAINT fk_ligand_area_area FOREIGN KEY (area_id) REFERENCES area(area_id);


--
-- DISEASE - AREA - STRAIN
--
ALTER TABLE ONLY disease_area_strain
    ADD CONSTRAINT fk_disease_area_strain_disease FOREIGN KEY (disease_id) REFERENCES disease(disease_id);

ALTER TABLE ONLY disease_area_strain
    ADD CONSTRAINT fk_disease_area_strain_area FOREIGN KEY (area_id) REFERENCES area(area_id);

ALTER TABLE ONLY disease_area_strain
    ADD CONSTRAINT fk_disease_area_strain_strain FOREIGN KEY (strain_id) REFERENCES strain(strain_id);


--
-- BINDING - STRAIN - ARTICLE
--


ALTER TABLE ONLY binding_strain_article
    ADD CONSTRAINT fk_binding_strain_article_binding FOREIGN KEY (binding_id) REFERENCES binding(binding_id);

ALTER TABLE ONLY binding_strain_article
    ADD CONSTRAINT fk_binding_strain_article_strain FOREIGN KEY (strain_id) REFERENCES strain(strain_id);

ALTER TABLE ONLY binding_strain_article
    ADD CONSTRAINT fk_binding_strain_article_article FOREIGN KEY (article_id) REFERENCES article(article_id);

------------------------------------------------------------------------------------
-- VIEW
------------------------------------------------------------------------------------

create or replace view v_binding_strain	as
    select 	b.binding_id,
		b.ligand_id,
		s.strain_id,
		b.lectin_id,
		b.database_id,
		b.fimbria_id,
		b.binding_notes,
		b.binding_strength_id		
    from binding b, strain s, binding_strain_article bsa
    where b.binding_id = bsa.binding_id 
    and s.strain_id = bsa.strain_id
    order by 1,2,3

------------------------------------------------------------------------------------
-- FUNCTION
------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION unique_binding()
  RETURNS trigger
  LANGUAGE plpgsql
AS
$body$
BEGIN
    if (TG_OP = 'INSERT') then
        if (
		exists(SELECT 1 FROM binding as b, strain as s, binding_strain_article as bsa 
			where b.ligand_id = NEW.ligand_id
			and s.strain_id = NEW.strain_id
			and b.lectin_id = NEW.lectin_id
			and b.database_id = NEW.database_id
			and b.fimbria_id = NEW.fimbria_id
			--and b.binding_notes  = NEW.binding_notes
			--and b.binding_strength_id = NEW.binding_strength_id		

			and b.binding_id = bsa.binding_id 
			and s.strain_id = bsa.strain_id
		) 
	)
          then
            RAISE EXCEPTION 'Duplicate';--'Can''t insert (%,%)', NEW.a, NEW.b;
        end if;
    return NEW;
    end if;
END;    
$body$

------------------------------------------------------------------------------------
-- TRIGGER
------------------------------------------------------------------------------------

  CREATE TRIGGER tgr_unique_binding BEFORE INSERT
      ON binding FOR EACH ROW
 EXECUTE PROCEDURE unique_binding();

------------------------------------------------------------------------------------
-- DATA
------------------------------------------------------------------------------------
INSERT INTO database(database_name)VALUES ('SB');
INSERT INTO database(database_name)VALUES ('CFG');


--
-- area_TYPE
--
INSERT INTO area_type(area_type_name)VALUES ('System');
INSERT INTO area_type(area_type_name)VALUES ('Organ');
INSERT INTO area_type(area_type_name)VALUES ('Tissue');
INSERT INTO area_type(area_type_name)VALUES ('Cell');
INSERT INTO area_type(area_type_name)VALUES ('Synthetic');
